ALTER TABLE company ADD COLUMN is_provider boolean;
ALTER TABLE company ALTER COLUMN is_provider SET DEFAULT false;
ALTER TABLE company ADD COLUMN is_client boolean;
ALTER TABLE company ALTER COLUMN is_client SET DEFAULT false;
ALTER TABLE company ADD COLUMN is_broker boolean;
ALTER TABLE company ALTER COLUMN is_broker SET DEFAULT false;

DROP FUNCTION sp_company_count(integer, character varying, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, oid, bytea, character varying, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_company_insert(character varying, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, oid, bytea, character varying, character varying, integer, boolean, integer, integer);
DROP FUNCTION sp_company_selectlist(integer, integer, integer, character varying, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, oid, bytea, character varying, character varying, integer, boolean, integer, timestamp without time zone, timestamp without time zone, integer, timestamp without time zone, timestamp without time zone, integer, boolean, timestamp without time zone, timestamp without time zone);
DROP FUNCTION sp_company_update(integer, character varying, boolean, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, character varying, integer, oid, bytea, character varying, character varying, integer, boolean, integer, integer, boolean, timestamp without time zone);


CREATE OR REPLACE FUNCTION sp_company_insert(
	_code	character varying(20) DEFAULT null,
	_is_competitor	boolean DEFAULT null,
	_abb_name	character varying(10) DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_telephone	character varying(20) DEFAULT null,
	_fax	character varying(50) DEFAULT null,
	_tax_code	character varying(25) DEFAULT null,
	_remark	character varying DEFAULT null,
	_contact_person_1	character varying(255) DEFAULT null,
	_contact_phone_1	character varying(50) DEFAULT null,
	_contact_mobile_1	character varying(50) DEFAULT null,
	_contact_email_1	character varying(255) DEFAULT null,
	_contact_person_2	character varying(255) DEFAULT null,
	_contact_phone_2	character varying(50) DEFAULT null,
	_contact_mobile_2	character varying(50) DEFAULT null,
	_contact_email_2	character varying(255) DEFAULT null,
	_id_place	integer DEFAULT null,
	_map_object	oid DEFAULT null,
	_map_file	bytea DEFAULT null,
	_map_path	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_creator	integer DEFAULT null,
	_is_provider	boolean DEFAULT null,
	_is_client	boolean DEFAULT null,
	_is_broker	boolean DEFAULT null
)RETURNS int AS $$
BEGIN
	INSERT INTO company(code,is_competitor,abb_name,short_name,full_name,foreign_name,address,telephone,fax,tax_code,remark,contact_person_1,contact_phone_1,contact_mobile_1,contact_email_1,contact_person_2,contact_phone_2,contact_mobile_2,contact_email_2,id_place,map_object,map_file,map_path,description,status,is_draft,notification,creator,updator,deletion_time,is_provider,is_client,is_broker)
	VALUES(_code,_is_competitor,_abb_name,_short_name,_full_name,_foreign_name,_address,_telephone,_fax,_tax_code,_remark,_contact_person_1,_contact_phone_1,_contact_mobile_1,_contact_email_1,_contact_person_2,_contact_phone_2,_contact_mobile_2,_contact_email_2,_id_place,_map_object,_map_file,_map_path,_description,_status,_is_draft,_notification,_creator,_creator,null,_is_provider,_is_client,_is_broker);
RETURN currval('company_id_seq'::regclass);
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_company_selectList (
	_limit	integer,
	_offset integer,
	_id	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_is_competitor	boolean DEFAULT null,
	_abb_name	character varying(10) DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_telephone	character varying(20) DEFAULT null,
	_fax	character varying(50) DEFAULT null,
	_tax_code	character varying(25) DEFAULT null,
	_remark	character varying DEFAULT null,
	_contact_person_1	character varying(255) DEFAULT null,
	_contact_phone_1	character varying(50) DEFAULT null,
	_contact_mobile_1	character varying(50) DEFAULT null,
	_contact_email_1	character varying(255) DEFAULT null,
	_contact_person_2	character varying(255) DEFAULT null,
	_contact_phone_2	character varying(50) DEFAULT null,
	_contact_mobile_2	character varying(50) DEFAULT null,
	_contact_email_2	character varying(255) DEFAULT null,
	_id_place	integer DEFAULT null,
	_map_object	oid DEFAULT null,
	_map_file	bytea DEFAULT null,
	_map_path	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_is_provider	boolean DEFAULT null,
	_is_client	boolean DEFAULT null,
	_is_broker	boolean DEFAULT null
)
RETURNS SETOF company
AS $$
BEGIN
	IF _limit IS NULL THEN
		_limit = 2147483647;
	END IF;
	IF _offset IS NULL THEN
		_offset = 0;
	END IF;
RETURN QUERY SELECT * FROM company WHERE
	 	(_id is null OR id = _id)	
	AND (_code is null OR code = _code)	
	AND (_is_competitor is null OR is_competitor = _is_competitor)	
	AND (_abb_name is null OR abb_name = _abb_name)	
	AND (_short_name is null OR short_name = _short_name)	
	AND (_full_name is null OR full_name = _full_name)	
	AND (_foreign_name is null OR foreign_name = _foreign_name)	
	AND (_address is null OR address = _address)	
	AND (_telephone is null OR telephone = _telephone)	
	AND (_fax is null OR fax = _fax)	
	AND (_tax_code is null OR tax_code = _tax_code)	
	AND (_remark is null OR remark = _remark)	
	AND (_contact_person_1 is null OR contact_person_1 = _contact_person_1)	
	AND (_contact_phone_1 is null OR contact_phone_1 = _contact_phone_1)	
	AND (_contact_mobile_1 is null OR contact_mobile_1 = _contact_mobile_1)	
	AND (_contact_email_1 is null OR contact_email_1 = _contact_email_1)	
	AND (_contact_person_2 is null OR contact_person_2 = _contact_person_2)	
	AND (_contact_phone_2 is null OR contact_phone_2 = _contact_phone_2)	
	AND (_contact_mobile_2 is null OR contact_mobile_2 = _contact_mobile_2)	
	AND (_contact_email_2 is null OR contact_email_2 = _contact_email_2)	
	AND (_id_place is null OR id_place = _id_place)	
	AND (_map_object is null OR map_object = _map_object)	
	AND (_map_file is null OR map_file = _map_file)	
	AND (_map_path is null OR map_path = _map_path)	
	AND (_description is null OR description = _description)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_is_provider is null OR is_provider = _is_provider)	
	AND (_is_client is null OR is_client = _is_client)	
	AND (_is_broker is null OR is_broker = _is_broker)	ORDER BY updated DESC  LIMIT _limit OFFSET _offset;
END;
$$ LANGUAGE 'plpgsql';


CREATE OR REPLACE FUNCTION sp_company_count (
	_id	integer DEFAULT null,
	_code	character varying(20) DEFAULT null,
	_is_competitor	boolean DEFAULT null,
	_abb_name	character varying(10) DEFAULT null,
	_short_name	character varying(25) DEFAULT null,
	_full_name	character varying(255) DEFAULT null,
	_foreign_name	character varying(255) DEFAULT null,
	_address	character varying(255) DEFAULT null,
	_telephone	character varying(20) DEFAULT null,
	_fax	character varying(50) DEFAULT null,
	_tax_code	character varying(25) DEFAULT null,
	_remark	character varying DEFAULT null,
	_contact_person_1	character varying(255) DEFAULT null,
	_contact_phone_1	character varying(50) DEFAULT null,
	_contact_mobile_1	character varying(50) DEFAULT null,
	_contact_email_1	character varying(255) DEFAULT null,
	_contact_person_2	character varying(255) DEFAULT null,
	_contact_phone_2	character varying(50) DEFAULT null,
	_contact_mobile_2	character varying(50) DEFAULT null,
	_contact_email_2	character varying(255) DEFAULT null,
	_id_place	integer DEFAULT null,
	_map_object	oid DEFAULT null,
	_map_file	bytea DEFAULT null,
	_map_path	character varying(255) DEFAULT null,
	_description	character varying DEFAULT null,
	_status	integer DEFAULT null,
	_is_draft	boolean DEFAULT null,
	_notification	integer DEFAULT null,
	_createdFrom	timestamp without time zone DEFAULT null,
	_createdTo	timestamp without time zone DEFAULT null,
	_creator	integer DEFAULT null,
	_updatedFrom	timestamp without time zone DEFAULT null,
	_updatedTo	timestamp without time zone DEFAULT null,
	_updator	integer DEFAULT null,
	_deleted	boolean DEFAULT null,
	_deletion_timeFrom	timestamp without time zone DEFAULT null,
	_deletion_timeTo	timestamp without time zone DEFAULT null,
	_is_provider	boolean DEFAULT null,
	_is_client	boolean DEFAULT null,
	_is_broker	boolean DEFAULT null
)
RETURNS int AS $$
BEGIN
	RETURN COUNT(*) FROM company WHERE
	 	(_id is null OR id = _id)	
	AND (_code is null OR code = _code)	
	AND (_is_competitor is null OR is_competitor = _is_competitor)	
	AND (_abb_name is null OR abb_name = _abb_name)	
	AND (_short_name is null OR short_name = _short_name)	
	AND (_full_name is null OR full_name = _full_name)	
	AND (_foreign_name is null OR foreign_name = _foreign_name)	
	AND (_address is null OR address = _address)	
	AND (_telephone is null OR telephone = _telephone)	
	AND (_fax is null OR fax = _fax)	
	AND (_tax_code is null OR tax_code = _tax_code)	
	AND (_remark is null OR remark = _remark)	
	AND (_contact_person_1 is null OR contact_person_1 = _contact_person_1)	
	AND (_contact_phone_1 is null OR contact_phone_1 = _contact_phone_1)	
	AND (_contact_mobile_1 is null OR contact_mobile_1 = _contact_mobile_1)	
	AND (_contact_email_1 is null OR contact_email_1 = _contact_email_1)	
	AND (_contact_person_2 is null OR contact_person_2 = _contact_person_2)	
	AND (_contact_phone_2 is null OR contact_phone_2 = _contact_phone_2)	
	AND (_contact_mobile_2 is null OR contact_mobile_2 = _contact_mobile_2)	
	AND (_contact_email_2 is null OR contact_email_2 = _contact_email_2)	
	AND (_id_place is null OR id_place = _id_place)	
	AND (_map_object is null OR map_object = _map_object)	
	AND (_map_file is null OR map_file = _map_file)	
	AND (_map_path is null OR map_path = _map_path)	
	AND (_description is null OR description = _description)	
	AND (_status is null OR status = _status)	
	AND (_is_draft is null OR is_draft = _is_draft)	
	AND (_notification is null OR notification = _notification)	
	AND (_createdFrom is null OR created >= _createdFrom)
	AND	(_createdTo is null OR created <= _createdTo)	
	AND (_creator is null OR creator = _creator)	
	AND (_updatedFrom is null OR updated >= _updatedFrom)
	AND	(_updatedTo is null OR updated <= _updatedTo)	
	AND (_updator is null OR updator = _updator)	
	AND (_deleted is null OR deleted = _deleted)	
	AND (_deletion_timeFrom is null OR deletion_time >= _deletion_timeFrom)
	AND	(_deletion_timeTo is null OR deletion_time <= _deletion_timeTo)	
	AND (_is_provider is null OR is_provider = _is_provider)	
	AND (_is_client is null OR is_client = _is_client)	
	AND (_is_broker is null OR is_broker = _is_broker);
END;
$$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION sp_company_update 
(	_id integer,
	_code character varying(20),
	_is_competitor boolean,
	_abb_name character varying(10),
	_short_name character varying(25),
	_full_name character varying(255),
	_foreign_name character varying(255),
	_address character varying(255),
	_telephone character varying(20),
	_fax character varying(50),
	_tax_code character varying(25),
	_remark character varying,
	_contact_person_1 character varying(255),
	_contact_phone_1 character varying(50),
	_contact_mobile_1 character varying(50),
	_contact_email_1 character varying(255),
	_contact_person_2 character varying(255),
	_contact_phone_2 character varying(50),
	_contact_mobile_2 character varying(50),
	_contact_email_2 character varying(255),
	_id_place integer,
	_map_object oid,
	_map_file bytea,
	_map_path character varying(255),
	_description character varying,
	_status integer,
	_is_draft boolean,
	_notification integer,
	_updator integer,
	_deleted boolean,
	_deletion_time timestamp without time zone,
	_is_provider boolean,
	_is_client boolean,
	_is_broker boolean  ) 
RETURNS INT AS 
$$
BEGIN 
	UPDATE company 
	SET 		code = _code,
		is_competitor = _is_competitor,
		abb_name = _abb_name,
		short_name = _short_name,
		full_name = _full_name,
		foreign_name = _foreign_name,
		address = _address,
		telephone = _telephone,
		fax = _fax,
		tax_code = _tax_code,
		remark = _remark,
		contact_person_1 = _contact_person_1,
		contact_phone_1 = _contact_phone_1,
		contact_mobile_1 = _contact_mobile_1,
		contact_email_1 = _contact_email_1,
		contact_person_2 = _contact_person_2,
		contact_phone_2 = _contact_phone_2,
		contact_mobile_2 = _contact_mobile_2,
		contact_email_2 = _contact_email_2,
		id_place = _id_place,
		map_object = _map_object,
		map_file = _map_file,
		map_path = _map_path,
		description = _description,
		status = _status,
		is_draft = _is_draft,
		notification = _notification,
		updated = now(),
		updator = _updator,
		deleted = _deleted,
		deletion_time = _deletion_time,
		is_provider = _is_provider,
		is_client = _is_client,
		is_broker = _is_broker
 	WHERE id = _id;
	RETURN 0;
END; 
$$ LANGUAGE plpgsql;
